package com.sojoys.artifact.plugin.syncdatabase;

import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.sql.DataSource;

import org.apache.commons.collections4.MapUtils;
import org.apache.commons.dbutils.QueryRunner;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.jfinal.plugin.IPlugin;
import com.sojoys.artifact.build.data.base.BaseDao;
import com.sojoys.artifact.constant.ConfigConstant;
import com.sojoys.artifact.manager.DataSourceManager;
import com.sojoys.artifact.manager.PropManager;
import com.sojoys.artifact.tools.SK_Plus;
import com.sojoys.artifact.tools.ToolMap;
import com.xiaoleilu.hutool.log.Log;
import com.xiaoleilu.hutool.log.LogFactory;
import com.xiaoleilu.hutool.util.ClassUtil;

/**
 * @author : DengYing
 * @CreateDate : 2017年7月3日 上午11:15:11
 * @Description ：同步Mysql数据库插件
 */
public class SyncMysqlPlugin implements IPlugin {
	static Log log = LogFactory.get(SyncMysqlPlugin.class);

	DataSource dataSource = null;

	@Override
	public boolean start() {
		dataSource = DataSourceManager.me().getMainSources();
		try {
			syncTables();
			return true;
		} catch (Exception e) {
			log.error(e);
		}
		return false;
	}

	@Override
	public boolean stop() {
		return true;
	}

	/**
	 * 同步表
	 * 
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws SecurityException
	 * @throws NoSuchMethodException
	 */
	@SuppressWarnings("rawtypes")
	public void syncTables() throws Exception {
		try (Connection conn = dataSource.getConnection()) {
			// 扫描所有的dao实例
			Set<Class<?>> scanClasses = ClassUtil.scanPackageBySuper(PropManager.get(ConfigConstant.CONFIG_SCAN_DAO),
					BaseDao.class);
			for (Class<?> clazz : scanClasses) {
				clazz = Class.forName(clazz.getName());
				Constructor cons = clazz.getDeclaredConstructor(null);
				cons.setAccessible(true);
				BaseDao<?, ?> dao = (BaseDao<?, ?>) cons.newInstance(null);

				clear(conn, dao);

				syncColumn(conn, dao);

				syncIndex(conn, dao);
			}
		}
	}

	/**
	 * 清理废弃的 索引 & 字段
	 * 
	 * @param conn
	 * @param dao
	 * @throws SQLException
	 */
	private void clear(Connection conn, BaseDao<?, ?> dao) throws SQLException {
		// 取得代码中的索引结构
		List<Map<String, Object>> codeIndexsInfo = dao.getIndexsInfo();
		// 取得逻辑库的索引结构
		List<Map<String, Object>> dbIndexsInfo = getDbIndexsInfo(conn, dao);
		
		Map<String, Index> codeIndexs = toIndexs(codeIndexsInfo);
		Map<String, Index> dbIndexs = toIndexs(dbIndexsInfo);
		
		for (Entry<String, Index> map : dbIndexs.entrySet()) {
			// 代码中不存在的索引
			if (!codeIndexs.containsKey(map.getKey())) {
				String sql = "";
				if ("PRIMARY".equals(map.getKey())) {
					sql = String.format("ALTER TABLE %s DROP PRIMARY KEY;", dao.getTableName());
				} else {
					sql = String.format("ALTER TABLE %s DROP INDEX %s;", dao.getTableName(), map.getKey());
				}
				execSql(conn, sql);
			}
		}

		
		// 字段
		List<Map<String, Object>> codeColumnsInfo = dao.getColumnsInfo();
		List<Map<String, Object>> dbColumnsInfo = getDbColumnsInfo(conn, dao);

		for (Map<String, Object> map : dbColumnsInfo) {
			String DATABASE_COLUMN_NAME = map.get("COLUMN_NAME").toString();
			boolean delete = true;
			for (Map<String, Object> map2 : codeColumnsInfo) {
				String CODE_COLUMN_NAME = map2.get("COLUMN_NAME").toString();
				if (DATABASE_COLUMN_NAME.equals(CODE_COLUMN_NAME)) {
					delete = false;
					break;
				}
			}

			if (delete) {
				String sql = String.format("ALTER TABLE `%s` DROP `%s`;", dao.getTableName(), DATABASE_COLUMN_NAME);
				execSql(conn, sql);
			}
		}
	}

	private List<Map<String, Object>> getDbColumnsInfo(Connection conn, BaseDao<?, ?> dao) throws SQLException {
		List<Map<String, Object>> list = Lists.newArrayList();
		DatabaseMetaData md = conn.getMetaData();
		ResultSet rs = md.getColumns(getDatabaseName(conn), "%", dao.getTableName(), "%");
		String sql = String.format("SELECT * FROM `%s` LIMIT 1", dao.getTableName());
		while (rs.next()) {
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet qrs = stmt.executeQuery();
			Map<String, Object> map = rsToMap(rs);
			map.putAll(getColumnSize(qrs, ToolMap.getInt("ORDINAL_POSITION", map)));
			list.add(map);
		}
		return list;
	}

	
	private List<Map<String, Object>> getDbIndexsInfo(Connection conn, BaseDao<?, ?> dao)throws SQLException {
		List<Map<String, Object>> list = Lists.newArrayList();
		DatabaseMetaData dmd = conn.getMetaData();
		ResultSet rs = dmd.getIndexInfo(getDatabaseName(conn), "%", dao.getTableName(), false, true);
		while (rs.next()) {
			list.add(rsToMap(rs));
		}
		return list;
	}

	/**
	 * 同步索引
	 * 
	 * @param conn
	 * @param dao
	 * @throws SQLException
	 */
	private void syncIndex(Connection conn, BaseDao<?, ?> dao) throws SQLException {
		// 取得代码中的索引结构
		List<Map<String, Object>> codeIndexsInfo = dao.getIndexsInfo();
		// 取得逻辑库的索引结构
		List<Map<String, Object>> dbIndexsInfo = getDbIndexsInfo(conn, dao);

		Map<String, Index> codeIndexs = toIndexs(codeIndexsInfo);

		Map<String, Index> databaseIndexs = toIndexs(dbIndexsInfo);

		for (Entry<String, Index> e : codeIndexs.entrySet()) {
			String indexName = e.getKey();
			Index codeIndex = e.getValue();
			Index databaseIndex = databaseIndexs.get(indexName);
			// 如果数据库中索引不存在，则创建索引
			SK_Plus sp = SK_Plus.newSK_Plus();
			sp.as("ALTER TABLE `", dao.getTableName(), "` ");
			if (databaseIndex != null) {
				if (codeIndex.unique == databaseIndex.unique && !Collections.disjoint(codeIndex.columnNames, databaseIndex.columnNames)) {
					continue;
				}
				if ("PRIMARY".equals(indexName)) {
					sp.as("DROP PRIMARY KEY , ");
				} else {
//					sp.as("DROP INDEX  `", databaseIndex.columnNames, "` , ");
					sp.as("DROP INDEX  `", databaseIndex.indexName, "` , ");
				}
			}
			/**
			 * ALTER TABLE `player` ADD INDEX ( `type` ) ; 聚集索引 ALTER TABLE
			 * `player` ADD UNIQUE (`uid`); 唯一索引 ALTER TABLE `player` ADD UNIQUE
			 * (`账号` ,`密码`); 复合索引 ALTER TABLE `player` DROP PRIMARY KEY ; ALTER
			 * TABLE `player` ADD PRIMARY KEY(`id`);
			 */
			if ("PRIMARY".equals(indexName)) {
				sp.as("ADD PRIMARY KEY ");
			} else {
				if (codeIndex.unique) {
					sp.as("ADD UNIQUE ");
				} else {
					sp.as("ADD INDEX ");
				}
			}
			sp.as(indexName,"(");
			int i = 0;
			for (String columnName : codeIndex.columnNames) {
				if (i != 0) {
					sp.as(",");
				}
				sp.as("`", columnName, "`");
				i++;
			}
			sp.as(");");
			execSql(conn, sp.e());
		}
	}

	private QueryRunner execSql(Connection conn, String sql) throws SQLException {
		QueryRunner run = new QueryRunner();
		log.info(sql);
		run.update(conn, sql);
		return run;
	}

	private Map<String, Index> toIndexs(List<Map<String, Object>> databaseIndexsInfo) {
		Map<String, Index> indexs = Maps.newLinkedHashMap();
		for (Map<String, Object> map : databaseIndexsInfo) {
			String indexName = ToolMap.getString("INDEX_NAME", map);
			String columnName = map.get("COLUMN_NAME").toString();
			boolean unique = ToolMap.getBoolean("NON_UNIQUE", map) ? false : true;

			Index index = indexs.get(indexName);
			if (index == null) {
				index = new Index();
				index.indexName = indexName;
				index.unique = unique;
				indexs.put(indexName, index);
			}
			index.columnNames.add(columnName);
		}
		return indexs;
	}

	

	/**
	 * 同步字段
	 * 
	 * @throws SQLException
	 */
	private void syncColumn(Connection conn, BaseDao<?, ?> dao) throws SQLException {
		List<Map<String, Object>> codeInfos = dao.getColumnsInfo();
		
		List<Map<String, Object>> dbInfos = getDbColumnsInfo(conn, dao);

		
		// 如果数据库没有该表信息，就创建表
		if (dbInfos.isEmpty()) {
			dao.createTable();
			log.info("create table {}", dao.getTableName());
			return;
		}
		
		
		// 上一个字段
		Column lastColumn = null;
		
		for (Map<String, Object> codeColumnMap : codeInfos) {
			// 代码字段
			Column codeColumn = Column.createForMap(codeColumnMap);
			// 数据库字段
			Column dbColumn = Column.createForMap(getColumnMap(dbInfos, codeColumn.name));
			
			SK_Plus sp = SK_Plus.newSK_Plus();
			sp.as("ALTER TABLE  `", dao.getTableName(), "` ");
			
			// 如果数据没有该字段则新加，否则修改
			boolean add = false;
			if (dbColumn == null) {
				add = true;
			} else {
				if(codeColumn.equals(dbColumn)){
					continue;
				}
			}
			
			
			String event = add ? "ADD `" : String.format("CHANGE `%s` `", dbColumn.name);
			
			// 时间类型不能指定长度
			switch (codeColumn.type) {
			case "DATE":
			case "DATETIME":
			case "TIMESTAMP":
			case "TIME":
			case "YEAR":
				sp.as(event, codeColumn.name, "` ", codeColumn.type, " ");
				break;
			default:
				sp.as(event, codeColumn.name, "` ", codeColumn.type, "( ", codeColumn.size, " ) ");
				break;
			}
			// 不予许为空
			if (!codeColumn.allowNull) {
				sp.a("NOT NULL ");
			}
			// 设置默认值
			if (!"".equals(codeColumn.defVal)) {
				sp.as("DEFAULT  '", codeColumn.defVal, "' ");
			}
			// 自增
			if (codeColumn.auto) {
				sp.as("AUTO_INCREMENT ");
			}
			// 注释
			if (!"".equals(codeColumn.remarks)) {
				sp.as("COMMENT  '", codeColumn.remarks, "' ");
			}
			
			if (add) {
				// 新加字段(表开头)
				if (lastColumn == null) {
					sp.as("FIRST ");
				} else {
					sp.as("AFTER  `", lastColumn.name, "` ");
				}
			}
			
			lastColumn = codeColumn;
			sp.a(";");
			execSql(conn, sp.e());
		}
	}

	// 取得指定的数据库字段
	private Map<String, Object> getColumnMap(List<Map<String, Object>> columnsInfo,String columnName) {
		for (Map<String, Object> database : columnsInfo) {
			String databaseColumnName = database.get("COLUMN_NAME").toString();
			if (columnName.equals(databaseColumnName)) {
				return database;
			}
		}
		return null;
	}

	/**
	 * 取得数据库名字
	 * @param conn
	 * @return
	 */
	private String getDatabaseName(Connection conn) {
		String databaseName = null;
		try {
			databaseName = conn.getCatalog();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return databaseName;
	}

	private Map<String, Object> rsToMap(ResultSet rs) {
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			ResultSetMetaData rsMd = rs.getMetaData();
			int count = rsMd.getColumnCount();
			for (int i = 1; i <= count; i++) {
				String keyName = rsMd.getColumnName(i);
				rsMd.getColumnClassName(i);
				Object obj = rs.getObject(i);
				map.put(keyName, obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	/**
	 * 取得字段长度
	 * @param rs
	 * @param i
	 * @return
	 * @throws SQLException
	 */
	public static final Map<String, Object> getColumnSize(ResultSet rs, int i) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int precision = rsmd.getPrecision(i);
		Map<String, Object> e = new HashMap<>();
		e.put("COLUMN_SIZE", precision);
		return e;
	}
	
	
	/**
	 * 索引
	 * @author win7
	 *
	 */
	class Index {
		/** 所有名称 */
		private String indexName;
		/** 是否唯一 */
		private boolean unique;
		/** 所有关联字段 */
		private List<String> columnNames = Lists.newArrayList();
	}

	/**
	 * 字段
	 * @author win7
	 *
	 */
	static class Column {
		/** 字段名 */
		private String name;
		/** 注释 */
		private String remarks;
		/** 字段类型 */
		private String type;
		/** 字段长度 */
		private int size;
		/** 是否自增 */
		private boolean auto;
		/** 默认值 */
		private String defVal;
		/** 允许为NULL */
		private boolean allowNull;
		
		private static Column createForMap(Map<String, Object> map){
			if (map==null) {
				return null;
			}
			Column column = new Column();
			column.name = MapUtils.getString(map, "COLUMN_NAME");
			column.remarks = MapUtils.getString(map, "REMARKS");
			column.type = MapUtils.getString(map, "TYPE_NAME");
			column.size = MapUtils.getIntValue(map, "COLUMN_SIZE");
			column.auto = MapUtils.getString(map, "IS_AUTOINCREMENT","NO").equals("YES") ? true : false;
			column.defVal = MapUtils.getString(map, "COLUMN_DEF","");
			column.allowNull = MapUtils.getString(map, "IS_NULLABLE","NO").equals("YES") ? true : false;
			return column;
		}

		@Override
		public int hashCode() {
			final int prime = 31;
			int result = 1;
			result = prime * result + (allowNull ? 1231 : 1237);
			result = prime * result + (auto ? 1231 : 1237);
			result = prime * result + ((defVal == null) ? 0 : defVal.hashCode());
			result = prime * result + ((name == null) ? 0 : name.hashCode());
			result = prime * result + ((remarks == null) ? 0 : remarks.hashCode());
			result = prime * result + size;
			result = prime * result + ((type == null) ? 0 : type.hashCode());
			return result;
		}

		@Override
		public boolean equals(Object obj) {
			if (this == obj)
				return true;
			if (obj == null)
				return false;
			if (getClass() != obj.getClass())
				return false;
			Column other = (Column) obj;
			if (allowNull != other.allowNull)
				return false;
			if (auto != other.auto)
				return false;
			if (defVal == null) {
				if (other.defVal != null)
					return false;
			} else if (!defVal.equals(other.defVal))
				return false;
			if (name == null) {
				if (other.name != null)
					return false;
			} else if (!name.equals(other.name))
				return false;
//			if (remarks == null) {
//				if (other.remarks != null)
//					return false;
//			} else if (!remarks.equals(other.remarks))
//				return false;
			if (size != other.size)
				return false;
			if (type == null) {
				if (other.type != null)
					return false;
			} else if (!type.equals(other.type))
				return false;
			return true;
		}
		
		
		
		
	}
}
